package org.anyline.simple.clickhouse;

import org.anyline.adapter.DataReader;
import org.anyline.data.adapter.DataReaderFactory;
import org.anyline.data.adapter.DriverAdapter;
import org.anyline.data.jdbc.adapter.JDBCAdapter;
import org.anyline.data.param.ConfigStore;
import org.anyline.data.param.init.DefaultConfigStore;
import org.anyline.data.run.Run;
import org.anyline.entity.*;
import org.anyline.entity.geometry.*;
import org.anyline.metadata.*;
import org.anyline.metadata.type.DatabaseType;
import org.anyline.metadata.type.init.StandardTypeMetadata;
import org.anyline.service.AnylineService;
import org.anyline.util.BasicUtil;
import org.anyline.util.ConfigTable;
import org.anyline.util.LogUtil;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@SpringBootTest
public class ClickhouseTest {
    private final Logger log = LoggerFactory.getLogger(ClickhouseTest.class);
    @Autowired
    private AnylineService service          ;
    @Autowired
    private JdbcTemplate jdbc               ;
    private Catalog catalog = null          ; //
    private Schema schema   = null          ; //相当于数据库名  查数据库列表 是用SHOW SCHEMAS 但JDBC con.getCatalog()返回数据库名 而con.getSchema()返回null
    private String table    = "crm_user"    ; // 表名

    @Test
    public void timeout(){
        //设置SQL执行的超时时间
        ConfigTable.SQL_QUERY_TIMEOUT = 1;
        ConfigTable.SQL_UPDATE_TIMEOUT = 1;
        DataSet set = new DataSet();
        for(int i=0; i<800; i++){
            DataRow row = new DataRow();
            row.put("CODE", i);
            set.add(row);
        }
        service.insert("crm_user", set);
        set = service.querys("crm_user");
    }
    @Test
    public void batch() throws Exception{
        Table tab = service.metadata().table(catalog, schema, this.table);
        //如果存在则删除
        if(null != tab){
            service.ddl().drop(tab);
        }
        tab = new Table(table);
        tab.addColumn("ID", "Int64", false, null).primary(true);
        tab.addColumn("CODE", "VARCHAR(50)");
        tab.addColumn("NAME", "VARCHAR(50)");
        service.ddl().save(tab);
        long id = System.currentTimeMillis();
        DataSet set = new DataSet();
        //插入10行
        for(int i=0; i<10; i++){
            DataRow row = new DataRow();
            row.put("ID", id + i);
            row.put("CODE", "C_"+id + i);
            row.put("NAME", "N_"+i);
            set.add(row);
        }
        service.insert(table ,set);
        set = new DataSet();
        for(int i=0; i<15; i++){
            DataRow row = new DataRow();
            if(i<10) {
                row.put("ID", id + i);
            }
            row.put("CODE", "C_"+id + i);
            row.put("NAME", "U_N"+i);
            set.add(row);
        }
        //save 15行，其中10行update 5行insert
        service.save(100,table ,set);
    }

    @Test
    public void tinyint() throws Exception{
        Table tab = service.metadata().table("tab_int");
        if(null != tab){
            service.ddl().drop(tab);
        }
        tab = new Table("tab_int");
        tab.addColumn("ID", "BIGINT").setAutoIncrement(true).setPrimary(true);
        tab.addColumn("TYPE_CODE", "tinyint");
        tab.addColumn("AGE", "int8");
        service.ddl().create(tab);
        DataRow row = new DataRow();
        row.put("TYPE_CODE", 0);
        service.insert(tab, row);
        row = service.query(tab);
        System.out.println(row);
    }

    @Test
    public void tinyint1() throws Exception{
        DataRow row = new DataRow();
        row.put("TYPE_CODE", 0);
        service.insert("tab_int", row);
        row = service.query("tab_int");
        System.out.println(row);
    }


    @Test
    public void order(){
        for(int i=0; i<20; i++){
            DataRow row = new DataRow();
            row.put("CODE", i);
            service.insert("crm_user", row);
        }
        PageNavi navi = new DefaultPageNavi();
        navi.order("ID","DESC");
        navi.setCurPage(2);
        service.querys("crm_user", navi);
    }
    @Test
    public void tables() throws Exception {
        List<Table> tables = service.metadata().tables(false, new Catalog(), new Schema("simple"), "crm_user", 1, Boolean.FALSE);
        System.out.println(tables);
    }

    @Test
    public void defaultDateTime() throws Exception {
        Table tab = init();
        tab.addColumn("REG_TIMES","DATETIME");

        tab.addColumn("YMDS", "DATE").setDefaultValue(DriverAdapter.SQL_BUILD_IN_VALUE.CURRENT_DATETIME);
        service.ddl().save(tab);
    }
    private Table init() throws Exception{
        ConfigTable.IS_THROW_CONVERT_EXCEPTION = true;
        Table tab = service.metadata().table(table);
        if(null != tab){
            service.ddl().drop(tab);
        }
        tab = new Table(table);
        tab.addColumn("ID", "BIGINT").setPrimary(true).setAutoIncrement(true).setComment("主键");
        tab.addColumn("CODE", "VARCHAR(20)").setComment("编号");
        tab.addColumn("REG_TIME","DATETIME");
        tab.addColumn("YMD", "DATE");
        tab.setPrimaryKey("ID");
        service.ddl().create(tab);
        tab = service.metadata().table(table);
        LinkedHashMap<String, Column> columns = tab.getColumns();
        for(Column column:columns.values()){
            System.out.println("COLUMN:"+column.getName()+" "+column.getFullType() + " " + column.getComment() + " PK:"+column.isPrimaryKey());
        }
        return tab;
    }


    @Test
    public void tabQuery() throws Exception{
        Table table = service.metadata().table("crm_user", false);
        if(null == table){
            table = new Table("crm_user");
            table.addColumn("ID","INT").setPrimary(true);
            table.addColumn("code","varchar(10)");
            table.addColumn("name","varchar(10)");
            service.ddl().create(table);
        }
        ConfigTable.SQL_QUERY_TIMEOUT = 1;
        List<Table> tables = service.metadata().tables(true, new Catalog(null), new Schema(null), "crm_user",1, false);
        System.out.println(tables);
    }

    @Test
    public void float_double() throws Exception{
        type("float");          //float        正常
        type("float(32)");      //float(32)    正常
        type("float(10)");      //float(10)    正常
        type("float(10,2)");    //float(10,2)  异常
        type("double");         //double       异常(会换成number)
        type("double(32)");     //double(32)   异常(会换成number)
        type("double(10)");     //double(10)   异常(会换成number)
        type("double(10,2)");   //double(10,2) 异常(会换成number)
    }
    private void type(String type){
        try {
            String sql = "CREATE TABLE TAB_" + System.currentTimeMillis() + "(id int, code "+type+")";
            service.execute(sql);
            System.out.println(LogUtil.format(type + " 正常", 32));
        }catch (Exception e){
            System.out.println(LogUtil.format(type + " 异常", 31));
        }
    }
    @Test
    public void pk1() throws Exception{
        Table table = service.metadata().table(this.table, false);
        if(null != table){
            service.ddl().drop(table);
        }
        table = new Table(this.table);
        table.addColumn("ID", "INT").setPrimary(true);
        table.addColumn("CODE", "int");
        service.ddl().create(table);
        table = service.metadata().table(this.table);

        PrimaryKey pk = new PrimaryKey();
        pk.setName("pk_code");
        pk.addColumn("CODE");
        table.setPrimaryKey(pk);
        service.ddl().save(table);
    }
    @Test
    public void columns1(){
        service.metadata().columns(false, new Catalog(), new Schema("simple"));
    }
    @Test
    public void pk() throws Exception{
        Table tab = service.metadata().table(this.table);
        if(null != tab){
            service.ddl().drop(tab);
        }
        tab = new Table(table);
        tab.addColumn("ID", "int").setPrimary(true);
        tab.addColumn("code", "varchar(10)");
        service.ddl().create(tab);
        tab = service.metadata().table(this.table);
        tab.getColumn("ID").setNewName("SID");
        try {
            //主键所在列 不能修改
            service.ddl().alter(tab);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    @Test
    public void test1(){
        List<Table> tabs = service.metadata().tables(false, catalog, new Schema("simple"), table, 1, Boolean.FALSE);
        System.out.println(tabs);
    }
    @Test
    public void sort() throws Exception{// 查询表结构
        Table table = service.metadata().table("t_test");
        if(null != table){
            service.ddl().drop(table);
        }
        table = new Table("t_test");
        // 根据不同数据库长度精度有可能忽略
        table.addColumn("CODE", "varchar(20)").setComment("编号");
        table.addColumn("SALARY", "decimal(10,2)").setComment("精度").setNullable(false);
        table.addColumn("ID", "bigint", 12, 11).primary(true).setComment("主键");
        table.addColumn("DEFAULT_NAME", "varchar(50)").setComment("名称").setDefaultValue("A");
        table.addColumn("NAME", "varchar(50)").setComment("名称");
        table.addColumn("O_NAME", "varchar(50)").setComment("原列表");
        table.addColumn("SALARY_12", "decimal(10,2)").setComment("精度").setNullable(false);
        table.addColumn("DEL_COL", "varchar(50)").setComment("删除");
        table.addColumn("CREATE_BY", "bigint").setComment("创建人");
        table.addColumn("CREATE_TIME", "datetime")
                .setComment("创建时间")
                .setDefaultCurrentDateTime(true); //设置默认时间
        table.addColumn("UPDATE_BY", "bigint").setComment("更新人");
        table.addColumn("UPDATE_TIME", "datetime").setOnUpdate("CURRENT_TIMESTAMP").setComment("更新时间");
        service.ddl().save(table);

        table = service.metadata().table("t_test");
        Map<String, Column> columns = table.getColumns();
        columns.get("ID").autoIncrement(true);
        columns.get("DEFAULT_NAME").delete();
        columns.get("NAME").delete();
        columns.get("O_NAME").delete();
        columns.put("ITEM0001", new Column("ITEM0001", "varchar(150)").setComment("新增字段0001"));
        columns.get("SALARY").delete();
        columns.put("ITEM0002", new Column("ITEM0002", "varchar(250)").setComment("新增字段0002"));

        service.ddl().save(table);

    }
    @Test
    public void test2() throws Exception{
        // 查询表结构
        Table table = service.metadata().table("t_test2");
        if(null != table){
            service.ddl().drop(table);
        }
        table = new Table("t_test2");
        // 根据不同数据库长度精度有可能忽略
        table.addColumn("CODE", "varchar(20)").setComment("编号");
        table.addColumn("ID", "bigint", 12, 11).primary(true).setComment("主键");
        table.addColumn("DEFAULT_NAME", "varchar(50)").setComment("名称").setDefaultValue("A");
        table.addColumn("NAME", "varchar(50)").setComment("名称");
        table.addColumn("O_NAME", "varchar(50)").setComment("原列表");
        table.addColumn("SALARY", "decimal(10,2)").setComment("精度").setNullable(false);
        table.addColumn("SALARY_12", "decimal(10,2)").setComment("精度").setNullable(false);
        table.addColumn("DEL_COL", "varchar(50)").setComment("删除");
        table.addColumn("CREATE_BY", "bigint").setComment("创建人");
        table.addColumn("CREATE_TIME", "datetime")
                .setComment("创建时间")
                .setDefaultValue(JDBCAdapter.SQL_BUILD_IN_VALUE.CURRENT_DATETIME);
        table.addColumn("UPDATE_BY", "bigint").setComment("更新人");
        table.addColumn("UPDATE_TIME", "datetime").setComment("更新时间");
        service.ddl().save(table);
        table = service.metadata().table("t_test2");
        Map<String, Column> columns = table.getColumns();
        columns.get("ID").autoIncrement(true);
        columns.put("CODE", new Column("CODE", "varchar(120)").setComment("编号222"));
        columns.get("DEFAULT_NAME").delete();
        columns.get("NAME").delete();
        columns.get("O_NAME").delete();
        columns.put("ITEM0001", new Column("ITEM0001", "varchar(150)").setComment("新增字段0001"));
        columns.get("SALARY").delete();
        columns.put("ITEM0002", new Column("ITEM0002", "varchar(250)").setComment("新增字段0002"));
        service.ddl().save(table);
    }
    @Test
    public void position() throws Exception{
        Table table = service.metadata().table(catalog, schema, this.table);
        if(null != table){
            service.ddl().drop(table);
        }
        table = new Table(this.table);
        table.addColumn("C2","INT").setPosition(2);
        table.addColumn("C1","INT").setPosition(1).setPrimary(1);
        table.addColumn("C3", "int").setPosition(3);
        table.addColumn("C", "int"); //没有设置排最后
        // C1 C2 C3 C
        service.ddl().create(table);

        table = new Table(this.table);
        //改成C1 C3 C C2
        //排序主要取决于位置最小的那一列，其他列一次排序
        //position只用来排序已知的列， 只有未设置after,before时0才表示首位
        table.addColumn("C3", "int").setPosition(0).setAfter("C1");
        table.addColumn("C", "int").setPosition(1); //没有设置排最后
        table.sort();
        service.ddl().save(table);
        table = service.metadata().table(this.table);
        LinkedHashMap<String, Column> columns = table.getColumns();
        for(Column c:columns.values()){
            System.out.println(c.getName());
        }
    }
    @Test
    public void maps(){
        PageNavi navi = new DefaultPageNavi();
        service.maps(table, navi.autoCount(true));
        System.out.println(navi.getTotalRow());
        ConfigStore configs = new DefaultConfigStore().autoCount(true);
        service.maps(table, configs);
        System.out.println(configs.getPageNavi().getTotalRow());
    }
    @Test
    public void version() throws SQLException {
        String name = jdbc.getDataSource().getConnection().getMetaData().getDatabaseProductName();
        String version = jdbc.getDataSource().getConnection().getMetaData().getDatabaseProductVersion();
        String catalog = jdbc.getDataSource().getConnection().getCatalog();
        String schema = jdbc.getDataSource().getConnection().getSchema();
        log.warn("\nname:{}\nversion:{}\ncatalog:{}\nschema:{}",name, version, catalog, schema);
    }

    @Test
    public void columns(){
        LinkedHashMap<String, Column> columns = null;
       Table table = service.metadata().table(this.table);
        columns = table.getColumns();
        for(Column column:columns.values()){
            log.warn("column:{},auto:{},comment:{}", column.getName(), column.isAutoIncrement(), column.getComment());
        }
       /* columns = service.metadata().columns(this.table);
        for(Column column:columns.values()){
            log.warn("column:{},auto:{},comment:{}", column.getName(), column.isAutoIncrement(), column.getComment());
        }*/
    }

    @Test
    public void info() {
        log.warn("\ntype:{}\ncatalog:{}\nschema:{}\ndatabase:{}\nproduct:{}\nversion:{}"
                ,service.metadata().type()
                ,service.metadata().catalog()
                ,service.metadata().schema()
                ,service.metadata().database()
                ,service.metadata().product()
                ,service.metadata().version()
        );
    }
    @Test
    public void alter() throws Exception{
        ddl();
        Table table = service.metadata().table(catalog, schema, this.table);
        Column column = table.getColumn("CODE");
        column.setType("varchar(100)");
        column.setNewName("CODE_NEW");

        Column c = new Column("C_"+System.currentTimeMillis());
        c.setType("int");
        table.addColumn(c);
        service.ddl().alter(table);

    }

    @Test
    public void rename() throws Exception{
        ddl();
        Table table = service.metadata().table(this.table);
        Column column = table.getColumn("CODE");
        column.setNewName("CODE_NEW");
        //table.addColumn(column);
        service.ddl().save(table);
        table.setNewName("test_rename_"+System.currentTimeMillis());
        service.ddl().save(table);
    }
    @Test
    public void column()throws Exception{
        ddl();
        LinkedHashMap<String, Column> cols = service.metadata().columns(false, null, new Schema("simple_crm"), table);
        //第二次应该从缓存中获取
        cols = service.metadata().columns(false, null, new Schema("simple_crm"), table);
        for(Column col:cols.values()){
            System.out.println(col.getSchema()+"."+col.getTableName(true)+"."+col.getName());
        }
        cols = service.metadata().columns(table);
        for(Column col:cols.values()){
            System.out.println(col.getSchema()+"."+col.getTableName(true)+"."+col.getName());
        }
    }
    @Test
    public void index(){
        Table table = new Table("simple_crm","base_user");
        LinkedHashMap<String, Index> map = service.metadata().indexes(table);
        System.out.println(map);
    }
    @Test
    public void insert(){
        DataRow row = new DataRow();
        row.put("CODE",1);
        service.insert("crm_user", row);
    }
    @Test
    public void override() throws Exception{
        //不支持override
        String tab = "tab_override";
        Table table = service.metadata().table(tab);
        if(null != table){
            service.ddl().drop(table);
        }
        //表必须有主键
        table = new Table(tab);
        table.addColumn("ID", "BIGINT").primary(true);
        table.addColumn("CODE", "varchar(10)");
        service.ddl().create(table);

        DataRow row = new DataRow();
        row.put("ID", 1);
        row.put("CODE", "C2");
        service.insert(tab, row);

        row = new DataRow();
        row.put("ID", 1);
        row.put("CODE", "C2");
        //相同主键覆盖，实际执行MERGE INTO，这时CODE被更新成C2
        service.insert(tab, row, new DefaultConfigStore().override(Boolean.TRUE));
        DataSet set = service.querys(tab);
        System.out.println(set);

        row = new DataRow();
        row.put("ID", 1);
        row.put("CODE", "C2");
        //不覆盖，会生成INSERT,因为重复所以抛出异常
        //service.insert(tab, row, new DefaultConfigStore().override(Boolean.FALSE));
    }
    @Test
    public void ddl() throws Exception{
        ConfigTable.IS_THROW_SQL_UPDATE_EXCEPTION = true; //遇到SQL异常直接抛出
        //检测表结构
        Table table = service.metadata().table(catalog, schema, this.table);
        //如果存在则删除
        if(null != table){
            service.ddl().drop(table);
        }
        //也可以直接删除(需要数据库支持 IF EXISTS)
        service.ddl().drop(new Table(catalog, schema, this.table));

        //再查询一次
        table = service.metadata().table(catalog, schema, this.table);
        Assertions.assertNull(table);

        //定义表结构
        table = new Table(catalog, schema, this.table);
        table.setComment("表备注");
        //添加列
        //自增长列 如果要适配多种数据库 autoIncrement 有必须的话可以设置起始值与增量 autoIncrement(int seed, int step)
        table.addColumn("ID", "INT", false, null).setComment("主键").autoIncrement(true).primary(true);
        table.addColumn("CODE", "VARCHAR(50)").setComment("编号");
        table.addColumn("NAME", "VARCHAR(50)").setComment("名称");
        //默认当前时间 如果要适配多种数据库 用 SQL_BUILD_IN_VALUE.CURRENT_DATETIME
        table.addColumn("REG_TIME", "datetime").setComment("注册时间").setDefaultValue(JDBCAdapter.SQL_BUILD_IN_VALUE.CURRENT_DATETIME);
        table.addColumn("DATA_VERSION", "double", false, 1.1).setComment("数据版本");
        table.setEngine("MergeTree()");
        //创建表
        service.ddl().create(table);

        //再查询一次
        table = service.metadata().table(catalog, schema, this.table);
        Assertions.assertNotNull(table);
    }
    @Test
    public void tests(){
        ConfigStore condition = new DefaultConfigStore();
        condition.and("A.ID = B.ID");
        service.query("SELECT * FROM crm_user AS A , crm_user AS B", condition);
    }

    @Test
    public void page(){

        PageNavi page = new DefaultPageNavi();
        page.setPageRows(2);
        page.setCurPage(3);
        //无论是否分页 都返回相同结构的DataSet
        DataSet set = service.querys(table, page);
        System.out.println(page.getTotalRow());
    }
    @Test
    public void dml() throws Exception{

        DataSet set = new DataSet();
        for(int i=1; i<10; i++){
            DataRow row = new DataRow();
            //只插入NAME  ID自动生成 REG_TIME 默认当时时间
            row.put("NAME", "N"+i);
            set.add(row);
        }
        long qty = service.insert(table, set);
        log.warn(LogUtil.format("[批量插入][影响行数:{}][生成主键:{}]", 36), qty, set.getStrings("ID"));
        Assertions.assertEquals(qty , 9);

        DataRow row = new DataRow();
        row.put("NAME", "N");
        //当前时间，如果要适配多种数据库环境尽量用SQL_BUILD_IN_VALUE,如果数据库明确可以写以根据不同数据库写成: row.put("REG_TIME","${now()}"); sysdate,getdate()等等
        row.put("REG_TIME", JDBCAdapter.SQL_BUILD_IN_VALUE.CURRENT_DATETIME);
        qty = service.insert(table, row);
        log.warn(LogUtil.format("[单行插入][影响行数:{}][生成主键:{}]", 36), qty, row.getId());
        Assertions.assertEquals(qty , 1);
        Assertions.assertNotNull(row.getId());


        //查询全部数据
        set = service.querys(table);
        log.warn(LogUtil.format("[query result][查询数量:{}]", 36), set.size());
        log.warn("[多行查询数据]:{}",set.toJSON());
        Assertions.assertEquals(10, set.size());

        //只查一行
        row = service.query(table);
        log.warn("[单行查询数据]:{}",row.toJSON());
        Assertions.assertNotNull(row);
        Assertions.assertEquals(row.getId(), "1");

        //查最后一行
        row = service.query(table, "ORDER BY ID DESC");
        log.warn("[单行查询数据]:{}",row.toJSON());
        Assertions.assertNotNull(row);
        Assertions.assertEquals(row.getInt("ID",10), 10);

        //更新
        //put覆盖了Map.put返回Object
        row.put("NAME", "SAVE NAME");

        //set由DataRow声明实现返回DataRow可以链式操作
        row.set("CODE", "SAVE CODE").set("DATA_VERSION", 1.2);

        //save根据是否有主键来判断insert | update
        //可以指定SAVE哪一列
        service.save(row, "NAME");
        service.save(row);
        row.put("NAME", "UPDATE NAME");

        /*
        * 注意这里的page一般不手工创建，而是通过AnylineController中的condition自动构造
        * service.querys("crm_user", condition(true, "ID:id","NAME:%name%", TYPE_CODE:[type]), "AGE:>=age");
        * true:表示分页 或者提供int 表示每页多少行
        * ID:表示数据表中的列
        * id:表示http提交的参数名
        * [type]:表示数组
        * */

        //分页查询
        //每页3行,当前第2页(下标从1开始)
        PageNavi page = new DefaultPageNavi(2, 3);

        //无论是否分页 都返回相同结构的DataSet
        set = service.querys(table, page);
        log.warn(LogUtil.format("[分页查询][共{}行 第{}/{}页]", 36), page.getTotalRow(), page.getCurPage(), page.getTotalPage());
        log.warn(set.toJSON());
        Assertions.assertEquals(page.getTotalPage() , 4);
        Assertions.assertEquals(page.getTotalRow() , 10);

        //模糊查询
        set = service.querys("crm_user", "NAME:%N%");
        log.warn(LogUtil.format("[模糊查询][result:{}]", 36), set.toJSON());
        set = service.querys("crm_user", "NAME:%N");
        log.warn(LogUtil.format("[模糊查询][result:{}]", 36), set.toJSON());
        set = service.querys("crm_user", "NAME:N%");
        log.warn(LogUtil.format("[模糊查询][result:{}]", 36), set.toJSON());

        //其他条件查询
        //in
        List<Integer> in = new ArrayList<>();
        in.add(1);
        in.add(2);
        in.add(3);
        ConfigStore condition = new DefaultConfigStore();
        condition.ands("ID", in);

        //not in
        condition.and(Compare.NOT_IN, "NAME", "N1");
        List<Integer> notin = new ArrayList<>();
        notin.add(10);
        notin.add(20);
        notin.add(30);
        condition.and(Compare.NOT_IN, "ID", notin);

        //between
        List<Integer> between = new ArrayList<>();
        between.add(1);
        between.add(10);
        condition.and(Compare.BETWEEN, "ID", between);

        // >=
        condition.and(Compare.GREAT_EQUAL, "ID", "1");

        //前缀
        condition.and(Compare.LIKE_PREFIX, "NAME", "N");

        set = service.querys("crm_user", condition);
        log.warn(LogUtil.format("[后台构建查询条件][result:{}]", 36), set.toJSON());
        Assertions.assertEquals(set.size() , 2);

        qty = service.count(table);
        log.warn(LogUtil.format("[总数统计][count:{}]", 36), qty);
        Assertions.assertEquals(qty , 10);


        //根据默认主键ID更新
        row.put("CODE",1001);
        //默认情况下 更新过的列 会参与UPDATE
        qty = service.update(row);
        log.warn(LogUtil.format("[根据主键更新内容有变化的化][count:{}]", 36), qty);


        //根据临时主键更新,注意这里更改了主键后ID就成了非主键，但未显式指定更新ID的情况下,ID不参与UPDATE
        row.setPrimaryKey("NAME");
        qty = service.update(row);
        log.warn(LogUtil.format("[根据临时主键更新][count:{}]", 36), qty);

        //显示指定更新列的情况下才会更新主键与默认主键
        qty = service.update(row,"NAME","CODE","ID");
        log.warn(LogUtil.format("[更新指定列][count:{}]", 36), qty);

        //根据条件更新
        ConfigStore store = new DefaultConfigStore();
        store.and(Compare.GREAT, "ID", "1")
                .and(" CODE > 1")
                .and("NAME IS NOT NULL");
        qty = service.update(row, store);
        log.warn(LogUtil.format("[根据条件更新][count:{}]", 36), qty);


        qty = service.delete(set);
        log.warn("[根据ID删除集合][删除数量:{}]", qty);
        Assertions.assertEquals(qty, set.size());

        //根据主键删除
        qty = service.delete(row);
        log.warn("[根据ID删除][删除数量:{}]", qty);
        Assertions.assertEquals(qty, 1);

        set = service.querys(table, "ID:2");
        qty = service.delete(table, "ID","2");
        log.warn("[根据条件删除][删除数量:{}]", qty);
        Assertions.assertEquals(qty, set.size());


        set = service.querys(table, "ID IN(2,3)");
        qty = service.deletes(table, "ID","2","3");
        log.warn("[根据条件删除][删除数量:{}]", qty);
        Assertions.assertEquals(qty, set.size());

    }

    @Test
    public void geometry() throws Exception{
        //to char(create time,"yyyy-mm-dd')
        ConfigTable.IS_PRINT_EXCEPTION_STACK_TRACE = true;
        ConfigTable.IS_AUTO_CHECK_METADATA = true;
        Table table = service.metadata().table("bs_geometry");
        if(null != table){
            service.ddl().drop(table);
        }
        table = new Table("bs_geometry");
        table.addColumn("ID", "BIGINT").autoIncrement(true).primary(true);
        table.addColumn("C_POINT", "POINT");
        table.addColumn("C_LINESTRING", "LINESTRING");
        table.addColumn("C_Polygon", "Polygon");
        table.addColumn("C_MultiPoint", "MultiPoint");
        table.addColumn("C_MultiLine", "MultiLine");
        table.addColumn("C_MultiPolygon", "MultiPolygon");
        table.addColumn("C_GeometryCollection", "GeometryCollection");
        service.ddl().create(table);
        DataRow row = new DataRow();
        //点
        row.put("C_POINT", new Point(120.1,36.2));

        //线
        LineString line = new LineString();
        line.add(new Point(1,1)).add(new Point(2,2)).add(new Point(3,3));
        row.put("C_LINESTRING", line);

        //面
        Polygon polygon = new Polygon();
        Ring out = new Ring();
        out.add(new Point(1,1)).add(new Point(2,2)).add(new Point(3, 6)).add(new Point(1,1));
        polygon.add(out);
        row.put("C_Polygon", polygon);

        //多点
        MultiPoint points = new MultiPoint();
        points.add(new Point(1, 2)).add(new Point(3,6));
        row.put("C_MultiPoint", points);
        //多线
        //多面
        //集合
        ConfigStore configs = new DefaultConfigStore();
        DataSet set = new DataSet();
       set.add( (DataRow) row.clone());
        set.add(row);
        service.insert("bs_geometry", set, configs);

        List<Run> runs = configs.runs();
        for (Run run:runs){
            System.out.println("无占位符 sql:"+run.getFinalQuery(false));
            System.out.println("占位符 sql:"+run.getFinalQuery());
            System.out.println("sql values:"+run.getValues());
        }
        DataReaderFactory.reg(DatabaseType.MySQL,null,new DataReader() {
            @Override
            public Object read(Object value) {
                return 123456;
            }
            public Object[] supports(){
                return new Object[]{byte[].class,"point", StandardTypeMetadata.POINT};
            }
        });
        row = service.query("bs_geometry");
        System.out.println(row);

     }
    @Test
    public void test(){
        Table table = service.metadata().table("crm_user");
        System.out.println(table);
        service.query("SELECT * FROM crm_user","ID>0");
    }
    @Test
    public void json() throws Exception{
        ConfigTable.IS_AUTO_CHECK_METADATA = true;
        Table table = service.metadata().table("tab_json");
        if(null != table){
            service.ddl().drop(table);
        }
        table = new Table("tab_json");
        table.addColumn("ID", "INT").setAutoIncrement(true).setPrimary(true);
        table.addColumn("CODES", "JSON");
        table.addColumn("NAME", "VARCHAR(20)");
        service.ddl().create(table);

        DataRow code = new DataRow();
        code.put("A",1);
        code.put("B", 2);
        DataRow row = new DataRow();
        row.put("NAME", "ZH1");
        row.put("CODES", code);
        service.insert(table, row);

        code = new DataRow();
        code.put("A",11);
        code.put("B", 22);
         row = new DataRow();
        row.put("NAME", "ZH2");
        row.put("CODES", code);
        service.insert(table, row);

        DataSet set = service.querys(table);
        System.out.println(set.toJSON());
    }
    @Test
    public void help() throws Exception{
        Connection con = jdbc.getDataSource().getConnection();
        System.out.println("\n--------------[metadata]------------------------");
        System.out.println("catalog:"+con.getCatalog());
        System.out.println("schema:"+con.getSchema());
        ResultSet set = con.getMetaData().getTables(null, null, table, "TABLE".split(","));
        ResultSetMetaData md = set.getMetaData();
        if (set.next()) {
            System.out.println("\n--------------[table metadata]------------------------");
            for (int i = 1; i <= md.getColumnCount(); i++) {
                String column = md.getColumnName(i);
                System.out.print(BasicUtil.fillRChar(column, " ",20) + " = ");
                Object value = set.getObject(i);
                System.out.println(value);
            }
        }
        set = jdbc.getDataSource().getConnection().getMetaData().getColumns(null, null, null, null);
        md = set.getMetaData();
        if (set.next()) {
            System.out.println("\n--------------[column metadata]------------------------");
            for (int i = 1; i <= md.getColumnCount(); i++) {
                String column = md.getColumnName(i);
                System.out.print(BasicUtil.fillRChar(column, " ",37) + " = ");
                Object value = set.getObject(i);
                System.out.println(value);
            }
        }

    }
}
